# -*- coding: utf-8 -*-

import os
import sqlite3
import random
from datetime import datetime, timedelta, date

def main():
    homePath = os.path.expandvars(u'%APPDATA%')
    dbPath = os.path.join(homePath, '.AccountBook')
    if not os.path.exists(dbPath):
        os.mkdir(dbPath)
    else:
        return

    conn = sqlite3.connect(os.path.join(dbPath, 'ab.db'))
    cur = conn.cursor()
    
    # Create table
    cur.executescript("""
        CREATE TABLE IF NOT EXISTS daily (t_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        t_categoryId INTEGER,
        t_type INTEGER,
        t_money REAL,
        t_date TEXT,
        t_time TEXT,
        t_isValid INTEGER DEFAULT 1);

        CREATE TABLE IF NOT EXISTS category (t_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        t_name TEXT,
        t_path TEXT);

        CREATE TABLE IF NOT EXISTS memo (t_date TEXT,
        t_memo TEXT,
        t_isValid INTEGER DEFAULT 1);
    """)

    # Init db data
    # Table category
    conn.executescript("""
        insert into category (t_name, t_path) values ('Rice', '');
        insert into category (t_name, t_path) values ('Smoke', '');
        insert into category (t_name, t_path) values ('Milk', '');
        insert into category (t_name, t_path) values ('Eat', '');
        insert into category (t_name, t_path) values ('Drink', '');
        insert into category (t_name, t_path) values ('Game', '');
    """)

    # Table daily
#    tDate = '2015/08/12'
#    cur.executescript("""
#        insert into daily(t_CategoryId, t_type, t_money, t_date, t_time) values(1, 0, 11.11, '{0}', '11:11:11');
#        insert into daily(t_CategoryId, t_type, t_money, t_date, t_time) values(2, 1, 21.11, '{1}', '11:11:11');
#        insert into daily(t_CategoryId, t_type, t_money, t_date, t_time) values(3, 0, 31.11, '{2}', '11:11:11');
#        insert into daily(t_CategoryId, t_type, t_money, t_date, t_time) values(4, 1, 41.11, '{3}', '11:11:11');
#        insert into daily(t_CategoryId, t_type, t_money, t_date, t_time) values(5, 0, 51.11, '{4}', '11:11:11');
#    """.format(tesc(-4), tesc(-3), tesc(-2), tesc(-1), tesc(0)))
#
    catg = cur.execute("""
        select count(t_id) from category;
    """)
    catgCount = catg.fetchone()[0]

    rday = 5
    sqlDaily = ''
    for n in range(10000):
        sqlDaily += """insert into daily(t_CategoryId, t_type, t_money, t_date, t_time) values({0}, {1}, 11.11, '{2}', '11:11:11');""".format(random.randint(1, catgCount), random.randint(0, 1), tesc(-random.randint(1, rday)))

    cur.executescript(sqlDaily)

    conn.commit()

    cur.close()

def tesc(n):
    dt = datetime.today() + timedelta(days=n)
    return dt.strftime('%Y/%m/%d')

if __name__ == '__main__':
    main()
    # tesc(-1)

